package net.cmkj.mine.util;

import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

public class ExportExcel {

    //显示的导出表的标题
    private String title;
    //导出表的列名
    private Map<String,String> headData ;
    
    private List<Record> listData;
    
    HttpServletResponse response;
    
    //构造方法，传入要导出的数据
    public ExportExcel(String title, Map<String,String> headData, List<Record> listData, HttpServletResponse response){
        this.listData = listData;
        this.headData = headData;
        this.title = title;
        this.response = response;
    }
            
    /*
     * 导出数据
     * */
    public void export() throws Exception{
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表
            
            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);
            
            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象

            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, (headData.size()-1));
            sheet.addMergedRegion(cellRangeAddress);

            RegionUtil.setBorderLeft(1, cellRangeAddress, sheet, workbook);
            RegionUtil.setBorderBottom(1, cellRangeAddress, sheet, workbook);
            RegionUtil.setBorderRight(1, cellRangeAddress, sheet, workbook);
            RegionUtil.setBorderTop(1, cellRangeAddress, sheet, workbook);

            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);
            
            // 定义所需列数
            int columnNum = headData.size();
            HSSFRow rowRowName = sheet.createRow(2);                // 在索引2的位置创建行(最顶端的行开始的第二行)
            
            int index=0;
            for (String h : headData.keySet()) {
            	HSSFCell cellRowName = rowRowName.createCell(index);                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(headData.get(h));
                cellRowName.setCellValue(text);                                    //设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle);
                index++;
            }
            
    		Record record = null;
            BufferedImage bufferImg = null;//图片
            ByteArrayOutputStream byteArrayOut = null;
            //将查询出的数据设置到sheet对应的单元格中
            for(int i=0;i<listData.size();i++){
            	record = listData.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(i+3);//创建所需的行数
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                int cellIndex = 0;
                for (String h : headData.keySet()) {
                	HSSFCell cell = row.createCell(cellIndex);
					cellIndex++;
					//按照每条记录匹配数据
                    String value = record.get(h) == null ? "" : record.get(h).toString();
                    if(value.startsWith("img:")){
                    	try{
	                        byteArrayOut = new ByteArrayOutputStream();
	                        bufferImg = ImageIO.read(new File(value.replace("img:","")));
	                        ImageIO.write(bufferImg, "png", byteArrayOut);
	                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
	                                (short)(cellIndex-1), i+3, (short) cellIndex, i+4);
	                        // 插入图片
	                        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut
	                                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    	}catch (Exception e) {
                    		cell.setCellValue("");
                            cell.setCellStyle(style);
						}

                    }else{
                        cell.setCellValue(value);
                        cell.setCellStyle(style);
                    }
				}
            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if(columnWidth<255*256){
                    sheet.setColumnWidth(colNum, columnWidth < 6000 ? 6000 : columnWidth);    
                }else{
                    sheet.setColumnWidth(colNum,12000 );
                }
                /*if(colNum == 0){
                    sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
                }else{
                    sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
                }*/
            }
            
            if(workbook !=null){
                try
                {
                    String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
                    String headStr = "attachment; filename=\"" + fileName + "\"";
                    response.setContentType("APPLICATION/OCTET-STREAM");
                    response.setHeader("Content-Disposition", headStr);
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        }
        
    }
    
    public void exportShop() throws Exception {
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表
            
            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            rowm.setHeight((short) (22.5*20));
            
            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            HSSFCellStyle columnTopStyle = getTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle titleStyle = getTitleStyle(workbook);//获取标题样式对象
            HSSFCellStyle style = getColumnStyle(workbook);//获取单元格样式对象

            CellRangeAddress serialno = new CellRangeAddress(0, 0, 0, 0);
            sheet.addMergedRegion(serialno);
            RegionUtil.setBorderLeft(1, serialno, sheet, workbook);
            RegionUtil.setBorderBottom(1, serialno, sheet, workbook);
            RegionUtil.setBorderRight(1, serialno, sheet, workbook);
            RegionUtil.setBorderTop(1, serialno, sheet, workbook);
            
            CellRangeAddress floor = new CellRangeAddress(0, 0, 1, 1);
            sheet.addMergedRegion(floor);
            RegionUtil.setBorderLeft(1, floor, sheet, workbook);
            RegionUtil.setBorderBottom(1, floor, sheet, workbook);
            RegionUtil.setBorderRight(1, floor, sheet, workbook);
            RegionUtil.setBorderTop(1, floor, sheet, workbook);
            
            CellRangeAddress shoptype = new CellRangeAddress(0, 0, 2, 2);
            sheet.addMergedRegion(shoptype);
            RegionUtil.setBorderLeft(1, shoptype, sheet, workbook);
            RegionUtil.setBorderBottom(1, shoptype, sheet, workbook);
            RegionUtil.setBorderRight(1, shoptype, sheet, workbook);
            RegionUtil.setBorderTop(1, shoptype, sheet, workbook);
            
            CellRangeAddress shop = new CellRangeAddress(0, 0, 3, 13);
            sheet.addMergedRegion(shop);
            RegionUtil.setBorderLeft(1, shop, sheet, workbook);
            RegionUtil.setBorderBottom(1, shop, sheet, workbook);
            RegionUtil.setBorderRight(1, shop, sheet, workbook);
            RegionUtil.setBorderTop(1, shop, sheet, workbook);
            
            CellRangeAddress pos = new CellRangeAddress(0, 0, 14, 15);
            sheet.addMergedRegion(pos);
            RegionUtil.setBorderLeft(1, pos, sheet, workbook);
            RegionUtil.setBorderBottom(1, pos, sheet, workbook);
            RegionUtil.setBorderRight(1, pos, sheet, workbook);
            RegionUtil.setBorderTop(1, pos, sheet, workbook);
            
            CellRangeAddress handle = new CellRangeAddress(0, 0, 16, 23);
            sheet.addMergedRegion(handle);
            RegionUtil.setBorderLeft(1, handle, sheet, workbook);
            RegionUtil.setBorderBottom(1, handle, sheet, workbook);
            RegionUtil.setBorderRight(1, handle, sheet, workbook);
            RegionUtil.setBorderTop(1, handle, sheet, workbook);
            
            CellRangeAddress posnet = new CellRangeAddress(0, 0, 24, 29);
            sheet.addMergedRegion(posnet);
            RegionUtil.setBorderLeft(1, posnet, sheet, workbook);
            RegionUtil.setBorderBottom(1, posnet, sheet, workbook);
            RegionUtil.setBorderRight(1, posnet, sheet, workbook);
            RegionUtil.setBorderTop(1, posnet, sheet, workbook);
            
            CellRangeAddress state = new CellRangeAddress(0, 0, 30, 34);
            sheet.addMergedRegion(state);
            RegionUtil.setBorderLeft(1, state, sheet, workbook);
            RegionUtil.setBorderBottom(1, state, sheet, workbook);
            RegionUtil.setBorderRight(1, state, sheet, workbook);
            RegionUtil.setBorderTop(1, state, sheet, workbook);
            
            CellRangeAddress note = new CellRangeAddress(0, 0, 35, 35);
            sheet.addMergedRegion(note);
            RegionUtil.setBorderLeft(1, note, sheet, workbook);
            RegionUtil.setBorderBottom(1, note, sheet, workbook);
            RegionUtil.setBorderRight(1, note, sheet, workbook);
            RegionUtil.setBorderTop(1, note, sheet, workbook);
            
            HSSFCell shoptitle = rowm.getCell(3);
            HSSFCell postitle = rowm.getCell(14);
            HSSFCell handletitle = rowm.getCell(16);
            HSSFCell posnettitle = rowm.getCell(24);
            HSSFCell statetitle = rowm.getCell(30);

            shoptitle.setCellStyle(columnTopStyle);
            shoptitle.setCellValue("租户信息");
            postitle.setCellStyle(columnTopStyle);
            postitle.setCellValue("POS参数");
            handletitle.setCellStyle(columnTopStyle);
            handletitle.setCellValue("手柄参数");
            posnettitle.setCellStyle(columnTopStyle);
            posnettitle.setCellValue("POS网络");
            statetitle.setCellStyle(columnTopStyle);
            statetitle.setCellValue("经营状态");
            
            // 定义所需列数
            int columnNum = headData.size();
            HSSFRow rowRowName = sheet.createRow(1);                // 在索引2的位置创建行(最顶端的行开始的第二行)
            rowRowName.setHeight((short) (33*20));
            
            int index=0;
            for (String h : headData.keySet()) {
            	HSSFCell cellRowName = rowRowName.createCell(index);                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(headData.get(h));
                cellRowName.setCellValue(text);                                    //设置列头单元格的值
                cellRowName.setCellStyle(titleStyle);
                index++;
            }
            
    		Record record = null;
            BufferedImage bufferImg = null;//图片
            ByteArrayOutputStream byteArrayOut = null;
            //将查询出的数据设置到sheet对应的单元格中
            for(int i=0;i<listData.size();i++){
            	record = listData.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(i+2);//创建所需的行数
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                int cellIndex = 0;
                for (String h : headData.keySet()) {
                	HSSFCell cell = row.createCell(cellIndex);
					cellIndex++;
					//按照每条记录匹配数据
                    String value = record.get(h) == null ? "" : record.get(h).toString();
                    if(value.startsWith("img:")){
                    	try{
	                        byteArrayOut = new ByteArrayOutputStream();
	                        bufferImg = ImageIO.read(new File(value.replace("img:","")));
	                        ImageIO.write(bufferImg, "png", byteArrayOut);
	                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
	                                (short)(cellIndex-1), i+3, (short) cellIndex, i+4);
	                        // 插入图片
	                        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut
	                                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    	}catch (Exception e) {
                    		cell.setCellValue("");
                            cell.setCellStyle(style);
						}

                    }else{
                        cell.setCellValue(value);
                        cell.setCellStyle(style);
                    }
				}
            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if(columnWidth<255*256){
                    sheet.setColumnWidth(colNum, columnWidth < 6000 ? 6000 : columnWidth);    
                }else{
                    sheet.setColumnWidth(colNum,12000 );
                }
                /*if(colNum == 0){
                    sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
                }else{
                    sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
                }*/
            }
            if(workbook !=null){
                try {
                    String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
                    String headStr = "attachment; filename=\"" + fileName + "\"";
                    response.setContentType("APPLICATION/OCTET-STREAM");
                    response.setHeader("Content-Disposition", headStr);
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        
    }
    
    /* 
     * 列头单元格样式
     */    
      public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
          
            // 设置字体
          HSSFFont font = workbook.createFont();
          //设置字体大小
          font.setFontHeightInPoints((short)11);
          //字体加粗
          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
          //设置字体名字 
          font.setFontName("Courier New");
          //设置样式; 
          HSSFCellStyle style = workbook.createCellStyle();
          //设置底边框; 
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          //设置底边框颜色;  
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          //设置左边框;   
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          //设置左边框颜色; 
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          //设置右边框; 
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          //设置右边框颜色; 
          style.setRightBorderColor(HSSFColor.BLACK.index);
          //设置顶边框; 
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          //设置顶边框颜色;  
          style.setTopBorderColor(HSSFColor.BLACK.index);
          //在样式用应用设置的字体;  
          style.setFont(font);
          //设置自动换行; 
          style.setWrapText(false);
          //设置水平对齐的样式为居中对齐;  
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
          //设置垂直对齐的样式为居中对齐; 
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
          
          return style;
          
      }
      
      public HSSFCellStyle getTopStyle(HSSFWorkbook workbook) {
          // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字 
        font.setFontName("微软雅黑");
        //设置样式; 
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框; 
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;  
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;   
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色; 
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框; 
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色; 
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框; 
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;  
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;  
        style.setFont(font);
        //设置自动换行; 
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐; 
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
        
    }
      
      public HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
          // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字 
        font.setFontName("微软雅黑");
        //设置样式; 
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        //设置底边框; 
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;  
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;   
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色; 
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框; 
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色; 
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框; 
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;  
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;  
        style.setFont(font);
        //设置自动换行; 
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐; 
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
      
      public HSSFCellStyle getColumnStyle(HSSFWorkbook workbook) {
          // 设置字体
          HSSFFont font = workbook.createFont();
          //设置字体大小
          //font.setFontHeightInPoints((short)10);
          //字体加粗
          //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
          //设置字体名字 
          font.setFontName("微软雅黑");
          //设置样式; 
          HSSFCellStyle style = workbook.createCellStyle();
          //设置底边框; 
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          //设置底边框颜色;  
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          //设置左边框;   
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          //设置左边框颜色; 
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          //设置右边框; 
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          //设置右边框颜色; 
          style.setRightBorderColor(HSSFColor.BLACK.index);
          //设置顶边框; 
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          //设置顶边框颜色;  
          style.setTopBorderColor(HSSFColor.BLACK.index);
          //在样式用应用设置的字体;  
          style.setFont(font);
          //设置自动换行; 
          style.setWrapText(false);
          //设置水平对齐的样式为居中对齐;  
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
          //设置垂直对齐的样式为居中对齐; 
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
          return style;
    }
      
      /*  
     * 列数据信息单元格样式
     */  
      public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
            // 设置字体
            HSSFFont font = workbook.createFont();
            //设置字体大小
            //font.setFontHeightInPoints((short)10);
            //字体加粗
            //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //设置字体名字 
            font.setFontName("Courier New");
            //设置样式; 
            HSSFCellStyle style = workbook.createCellStyle();
            //设置底边框; 
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //设置底边框颜色;  
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //设置左边框;   
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            //设置左边框颜色; 
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            //设置右边框; 
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //设置右边框颜色; 
            style.setRightBorderColor(HSSFColor.BLACK.index);
            //设置顶边框; 
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //设置顶边框颜色;  
            style.setTopBorderColor(HSSFColor.BLACK.index);
            //在样式用应用设置的字体;  
            style.setFont(font);
            //设置自动换行; 
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;  
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //设置垂直对齐的样式为居中对齐; 
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
           
            return style;
      
      }

}